import sqlite3
import re


class DbHandle:
    def __init__(self, logger):
        self.logger = logger
        self.db_conn = None
        self.con = None

    def db_connect(self, info):
        self.db_conn = None
        try:
            self.db_conn = sqlite3.connect(info, isolation_level="DEFERRED", check_same_thread=False)
            self.con = self.db_conn.cursor()
            self.logger.info("connected")
        except:
            self.logger.info("connect failed.")
            quit()

    def init_database(self):
        try:
            sql1 = """create table if not exists urls
            (url text primary key, title text, inurl integer default 0, outurl integer default 0, response integer default 0)
            """
            self.con.execute(sql1)
            self.logger.info("init success.")
        except:
            self.logger.info("init failed.")
            pass

    def insert_one(self, info, table):
        sql1 = "insert or ignore into `%s` "%table
        name = []
        value = []
        try:
            for k, v in info.items():
                name.append("'%s'"%(str(k)))
                value.append("'%s'"%(str(v)))
        except UnicodeEncodeError:
            pass
        name_str = ','.join(name)
        value_str = ','.join(value)
        sql2 = "(%s) values(%s)"%(name_str, value_str)
        sql = sql1 + sql2
        print(sql)
        try:
            self.con.execute(sql)
            self.db_conn.commit()
        except sqlite3.IntegrityError:
            self.logger.info(sql)
            pass
        except:
            pass

    def commit_all(self):
        try:
            self.db_conn.commit()
        except sqlite3.IntegrityError:
            self.logger.info("commit_all failed.")
        except:
            pass

    def update_one(self, info, table, where = ''):
        # sql1 = "update  `%s` "%table
        # part = []
        # for k,v in info.iteritems():
        #     part.append("`%s` = '%s'"%(k,str(v)))
        # partstr = ",".join(part)
        # sql = sql1+" set "+partstr + " where url = '%s' "%((str(info['url'])))
        print
        "*******************************************"
        try:
            self.con.execute("""update `urls` set title=?,inurl=?,outurl=?,response=? where url = ?
        """, (info['title'], info['inurl'], info['outurl'], info['response'], info['url']))
            self.dbcon.commit()
        except TypeError:  # sqlite3.IntegrityError:
            pass

    def select_urls(self):
        info = {}
        allurl = []
        sql = "select url from urls where response = '0'"
        self.con.execute(sql)
        for i in self.con.fetchall():
            info['url'] = i[0]
            allurl.append(info)
            info = {}

        return allurl

    def getone(self,table,var = '*', where = ''):
        sql = "select "+var+" from "+table+where
        self.con.execute(sql)
        result = self.con.fetchone()

        return result[1]

    def geturls(self,info,workid):
        """
        info['did']  deepth
        info['url']  link
        """
        sql = "insert into urls (url,did,wid) values ('%s','%s','%s')"%(info['url'],info['did'],workid)
        try:
            self.con.execute(sql)
            self.dbcon.commit()
        except sqlite3.IntegrityError:
            pass
        except:
            self.logger.error(str(info['did'])+"Éî¶È"+str(info['url'])+" failed to insert.")